package com.bysj.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.bysj.dao.GuideDao;
import com.bysj.entity.Guide;
import com.bysj.entity.RenCai;
import com.bysj.util.Dbutils;

public class GuideDaoImpl implements GuideDao{
	private Dbutils db=new Dbutils();
	@Override
	public void addGuide(String sql, Guide g) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, g.getTitle());
			pst.setString(2, g.getType());
			pst.setString(3, g.getContent());
			pst.setString(4, g.getTime());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public void delGuide(String sql, Guide g) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, g.getId());
			pst.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public void updateGuide(String sql, Guide g) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;

		try {
			pst=conn.prepareStatement(sql);

			///////
			pst.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public ArrayList<Guide> queryGuide(String sql) {
		ArrayList<Guide> list=new ArrayList<Guide>();
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Guide g=null;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			while(set.next()){
				g=new Guide();
				g.setId(set.getInt(1));
				g.setTitle(set.getString(2));
				g.setType(set.getString(3));
				g.setContent(set.getString(4));
				g.setTime(set.getString(5));
				g.setDocname(set.getString(6));
				list.add(g);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

	@Override
	public int CountGuide(String sql) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		int count=0;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			if(set.next()){
				count=set.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return count;
	}

	@Override
	public ArrayList<Guide> FenyeQuery(String sql, int page, int rows) {
		ArrayList<Guide> list=new ArrayList<Guide>();
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Guide g=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			set=pst.executeQuery();
			while(set.next()){
				g=new Guide();
				g.setId(set.getInt(1));
				g.setTitle(set.getString(2));
				g.setType(set.getString(3));
				g.setContent(set.getString(4));
				g.setTime(set.getString(5));
				g.setDocname(set.getString(6));
				list.add(g);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

	@Override
	public ArrayList<Guide> queryGuideByType(String sql, Guide guide) {
		ArrayList<Guide> list=new ArrayList<Guide>();
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Guide g=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, guide.getType());
			set=pst.executeQuery();
			while(set.next()){
				g=new Guide();
				g.setId(set.getInt(1));
				g.setTitle(set.getString(2));
				g.setType(set.getString(3));
				g.setContent(set.getString(4));
				g.setTime(set.getString(5));
				g.setDocname(set.getString(6));
				list.add(g);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}

	@Override
	public Guide queryGuideByid(String sql, Guide guide) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Guide g=new Guide();
		try {
			pst=conn.prepareStatement(sql);
			pst.setInt(1, guide.getId());;
			set=pst.executeQuery();
			if(set.next()){
				
				g.setId(set.getInt(1));
				g.setTitle(set.getString(2));
				g.setType(set.getString(3));
				g.setContent(set.getString(4));
				g.setTime(set.getString(5));
				g.setDocname(set.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return g;
	}

	@Override
	public void updateGuideDocById(String sql, Guide g) {
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			pst.setString(1, g.getDocname());
			pst.setInt(2, g.getId());
			pst.execute();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(null, pst, conn);
		}
	}

	@Override
	public ArrayList<Guide> queryGuideLikeByTitle(String sql) {
		ArrayList<Guide> list=new ArrayList<Guide>();
		Connection conn=db.getConnection();
		PreparedStatement pst=null;
		ResultSet set=null;
		Guide g=null;
		try {
			pst=conn.prepareStatement(sql);
			set=pst.executeQuery();
			while(set.next()){
				g=new Guide();
				g.setId(set.getInt(1));
				g.setTitle(set.getString(2));
				g.setType(set.getString(3));
				g.setContent(set.getString(4));
				g.setTime(set.getString(5));
				g.setDocname(set.getString(6));
				list.add(g);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeAll(set, pst, conn);
		}
		return list;
	}
}
